/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package model;

import entity.Department;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import utility.DatabaseConnection;

/**
 *
 * @author duydv_a04437
 */
public class DepartmentModel {

    private String sql = null;
    private Connection con = null;
    private CallableStatement cs = null;
    private PreparedStatement ps = null;
    private ResultSet rs = null;

    public DepartmentModel(){

    }

    public ArrayList<Department> getAllDepartment(){
        ArrayList<Department> lst = new ArrayList<Department>();
        try {
            con = DatabaseConnection.getConnection();
            sql = "select * from Department";
            ps = con.prepareStatement(sql);
            rs = ps.executeQuery();
            while (rs.next()) {
                Department d = new Department();
                d.setDepartmentid(rs.getString(1));
                d.setDepartmentname(rs.getString(2));
                lst.add(d);
            }
            return lst;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    public  Department getOneDepartment(String id){
        Department d = null;
        try {
            con = DatabaseConnection.getConnection();
            sql = "select * from Department where DepartmentID =?";
            ps = con.prepareStatement(sql);
            ps.setString(1, id);
            rs = ps.executeQuery();
            while (rs.next()) {
                d = new Department();
                d.setDepartmentid(rs.getString(1));
                d.setDepartmentname(rs.getString(2));
            }

            return d;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    public boolean insertDepartment(Department d){
        try {
            con = DatabaseConnection.getConnection();
            sql = "insert into Department values(?,?)";
            ps = con.prepareStatement(sql);
            ps.setString(1, d.getDepartmentid());
            ps.setString(2, d.getDepartmentname());

            if (ps.executeUpdate() > 0) {
                return true;
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return false;
    }

    public boolean updateDepartment(Department d){
        try {
            con = DatabaseConnection.getConnection();
            sql = "update Department set DepartmentName=? where DepartmentID=?";
            ps = con.prepareStatement(sql);
            ps.setString(1, d.getDepartmentname());
            ps.setString(2, d.getDepartmentid());

            if (ps.executeUpdate() > 0) {
                return true;
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return false;
    }

    public boolean deleteDepartment(String id){
        try {
            con = DatabaseConnection.getConnection();
            sql = "delete from Department where DepartmentID=?";
            ps = con.prepareStatement(sql);
            ps.setString(1, id);

            if (ps.executeUpdate() > 0) {
                return true;
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return false;
    }

    public ArrayList<Department> getsub2Department(){
        ArrayList<Department> lst = new ArrayList<Department>();
        try {
            con = DatabaseConnection.getConnection();
            sql = "select * from Department where DepartmentID != 'D0001' and DepartmentID != 'D0002'";
            ps = con.prepareStatement(sql);
            rs = ps.executeQuery();
            while (rs.next()) {
                Department d = new Department();
                d.setDepartmentid(rs.getString(1));
                d.setDepartmentname(rs.getString(2));
                lst.add(d);
            }
            return lst;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }
}
